[初心者向け]BigQueryのウィンドウ関数（分析関数）におけるウィンドウフレームについて簡潔にまとめてみた

GoogleCloud(GCP)のサービスであるBigQueryを用いて、初心者向けにウィンドウ関数のウィンドウフレームについて説明しています。

#Google Cloud (GCP)

#Google BigQuery

ニューシロ

2023.04.21

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

クラスメソッド株式会社データアナリティクス事業本部所属のニューシロです。
今回はBigQueryのウィンドウ関数（分析関数）におけるウィンドウフレームについて簡潔にまとめてみました。

前提

この記事の対象者

ウィンドウ関数はなんとなくわかる気がするけど、ウィンドウフレームがよくわからない人

この記事で説明すること

ウィンドウフレームについて（ざっくりと）

この記事で説明しないこと

ウィンドウ関数について
PARTITION BY, ORDER BYについて
RANGEを用いるウィンドウフレームについて
名前付きウィンドウについて

本題

ウィンドウ関数の基本構文

ウィンドウ関数の基本構文は以下の通りです。

function_name ( [ argument_list ] ) OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

window_frame_clause:
  { rows_range } { frame_start | frame_between }

rows_range:
  { ROWS | RANGE }

引用元：Window function calls | BigQuery | Google Cloud - Window function syntax

ものすごく簡単に書きます。

↓

使いたい関数名　OVER (
  PARTITION BY　仕切りたい列名
  ORDER BY 並べ替えの基準とする列名
  ウィンドウフレーム
  )

今回説明するウィンドウフレームは4行目にあります。

ウィンドウフレームについて

以下は公式ドキュメントの引用です。

Window Frame 句は、ウィンドウ関数が評価されるパーティション内の現在の行を囲むウィンドウフレームを定義します。集計分析関数でのみ Window Frame 句を使用できます。

引用元：Window function calls | BigQuery | Google Cloud - Defining the window frame clause

一言で表すと、ウィンドウフレームはウィンドウ関数の対象を絞り込むためにあります。
しかし、ウィンドウ関数について少し学んだ方でしたら、こんな疑問を持つ方もいるかもしれません。PARTITION BYですでに対象は絞り込んでいるじゃないか、、と。ちなみに私は初めて学んだときに思いました。そうです、既に分けているのですが、そのパーティションの中で、対象をさらに絞り込むためにウィンドウフレームがあるのです。では具体例を挙げてウィンドウフレームについて学んでいきたいのですが、その前に一つ注意点です。

ウィンドウフレームは、使える関数と使えない関数があります（初めてウィンドウ関数を学んだ際、私はこれを知らずに学習していたので混乱してしまいました）。例えばウィンドウ関数で代表的なRANKは、ウィンドウフレームが使えません。ウィンドウ関数を用いる場合、まずはウィンドウフレームが利用可能かどうか調べましょう。　　

ウィンドウフレームを用いた具体例

今回はSUMを用いて具体例を見ていきます。本題から逸れないよう、今回PARTITION BYは用いません。なるべくシンプルにしたいので、以下のテーブルを作成しました。内容はクラスメソッド株式会社のここ5年間の売上高です。（単位：百万円）

参照：財務ハイライト | クラスメソッド株式会社

余談ではありますが、クラスメソッドのHPがリニューアルしたのでこちらも是非どうぞ！

クラスメソッド株式会社公式HP

本題に戻ります。ウィンドウフレームは、以下の形式になります(※ROWSの他にRANGEもありますが、今回は割愛します)。

ROWS BETWEEN + (範囲の始まり) + AND + (範囲の終わり)

(範囲の始まり) または (範囲の終わり) については、下記の表のいずれかが入ります。

(範囲の始まり) または (範囲の終わり)	意味 (ROWSを用いる場合)
UNBOUNDED PRECEDING	そのパーティション内の一番上の行
(整数値) PRECEDING	現在の行から(整数値) 行だけ上
CURRENT ROW	現在の行
(整数値) FOLLOWING	現在の行から(整数値) 行だけ下
UNBOUNDED FOLLOWING	そのパーティション内の一番下の行

参照：Window function calls | BigQuery | Google Cloud - Defining the window frame clause

まずはROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWというウィンドウフレームを用いて、SQLを実行してみましょう。

SELECT
  *,
  SUM(sales) OVER (
    ORDER BY year
　　　　　　　　ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) as sum_sales
FROM
  newshiro.developersio.classmethod_sales
ORDER BY
  year

結果

↓

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWは、上記の表より、
UNBOUNDED PRECEDING : 「そのパーティション内（今回は指定していないので黄枠）の一番上の行」から
CURRENT ROW : 「現在の行」
までをウィンドウフレームとして定義していることがわかります。よって、例えば2021年度のsum_salesに着目すると、結果の画像の茶枠部分の合計が表示されているというわけです。

補足ですが、ウィンドウフレームは書かない場合、省略されているという扱いになり、RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWが適用されます（RANGEについては今回割愛させていただきます)。書かなくても大丈夫というところが、ウィンドウフレームのややこしいところでもあります。。

もう一つ例を挙げましょう。先ほどのウィンドウフレームの範囲の始まりだけ変更して、
ROWS BETWEEN 2 PRECEDING AND CURRENT ROWとしてみましょう。　　

SELECT
  *,
  SUM(sales) OVER (
    ORDER BY year
　　　　　　　　ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) as sum_sales
FROM
  newshiro.developersio.classmethod_sales
ORDER BY
  year

結果

↓

これは
2 PRECEDING : 「現在の行から2行だけ上」から
CURRENT ROW : 「現在の行」
までをウィンドウフレームとして定義しています。適用範囲はパーティション内（今回は指定していないので黄枠）です。よって、2021年度のsum_salesに着目すると、2行前にある2019年度から現在の行である2021年度まで（青枠部分）の合計が表示されているというわけです。他にもAVGで用いると、過去3年分の平均を出したりすることができるので、便利ですね。

理解できたでしょうか。初めはなんとなく理解して、あとは手を実際に動かしてみることが大事です。おすすめの参考書を一冊あげておきます。練習問題が豊富で、多くの問題を解くことでウィンドウ関数の理解につながる良い本です。